package com.yang.dao;

import com.yang.pojo.Brand;
import com.yang.pojo.good;
import com.yang.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BrandDao {
    //根据分类id查询品牌列表
    public ArrayList<Brand> selectBrandByCategoryId(int categoryId) throws SQLException {
        ArrayList<Brand> brands = new ArrayList<>();
        String sql = "select b.brand_id brandId,b.brand_name\n" +
                "brandName,b.brand_logo brandLogo,b.brand_desc brandDesc,b.create_time\n" +
                "createTime,b.brand_status brandStatus from tb_category_brand cb INNER\n" +
                "JOIN tb_brand b ON cb.fk_brand_id = b.brand_id where\n" +
                "cb.fk_category_id=?";

        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        brands = (ArrayList<Brand>) queryRunner.query(sql, new BeanListHandler<Brand>(Brand.class), categoryId);
        return brands;
    }

    //根据查询品牌信息
    public ArrayList<Brand> selectBrand() throws SQLException {
        ArrayList<Brand> brandList = new ArrayList<>();
        String sql = "SELECT brand_id brandId,brand_name brandName,brand_logo brandLogo,brand_desc brandDesc,create_time createTime,brand_status brandStatus FROM tb_brand";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        brandList = (ArrayList<Brand>) queryRunner.query(sql, new BeanListHandler<Brand>(Brand.class));
        return brandList;
    }
    //根据id删除品牌信息
    public int deleteBrandById(int brandId) throws SQLException {
        int i = 0;
        String sql = "DELETE FROM tb_brand WHERE brand_id = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, brandId);
        return i;
    }
    //根据当前品牌id查询商品信息
    public ArrayList<good> selectGoodByBrandId(int brandId) throws SQLException {
        ArrayList<good> goods = new ArrayList<>();
        String sql = "SELECT good_id goodId,good_name goodName,good_img goodImg,good_cost goodCost,good_min_price goodMinPrice,good_first_price goodFirstPrice,good_second_price goodSecondPrice,good_third_price goodThirdPrice,good_forth_price goodForthPrice,good_fifth_price goodFifthPrice\n" +
                " FROM tb_brand,tb_brand_good,tb_good WHERE tb_brand.brand_id = tb_brand_good.fk_brand_id AND tb_brand_good.fk_good_id= tb_good.good_id AND brand_id = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        goods = (ArrayList<good>) queryRunner.query(sql,new BeanListHandler<good>(good.class),brandId);
        return goods;
    }
    //根据brandId删除tb_category_brand信息
    public int deleteBrandAndCategoryByBrandId(int brandId) throws SQLException {
        int i = 0;
        String sql = "DELETE FROM tb_category_brand WHERE fk_brand_id = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, brandId);
        return i;
    }

    //根据品牌id修改品牌状态
    public int updateBrandStatus(int brandStatus,int brandId) throws SQLException {
        int i=0;
        String sql = "update tb_brand set brand_status=? where brand_id=?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, brandStatus, brandId);
        return i;
    }

    //保存品牌信息
    public int insertBrand(String brandName,String brandLogo,String brandDesc) throws SQLException {
        int brandId = 0;
        String sql = "INSERT INTO tb_brand(brand_name,brand_logo,brand_desc,brand_status) VALUES(?,?,?,1)";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Object object = queryRunner.insert(sql, new ScalarHandler<>(), brandName, brandLogo, brandDesc);
        brandId = Integer.parseInt(String.valueOf(object));
        return brandId;
    }

    //保存品牌和分类的关系信息
    public int insertCategoryAndBrand(int categoryId,int brandId) throws SQLException {
        int i=0;
        String sql = "insert into tb_category_brand(fk_category_id,fk_brand_id) values(?,?)";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, categoryId, brandId);
        return i;
    }

    //根据id查询品牌信息
    public Brand selectBrandById(int brandId) throws SQLException {
        String sql = "SELECT brand_id brandId,brand_name brandName,brand_logo brandLogo,brand_desc brandDesc,create_time createTime,brand_status brandStatus FROM tb_brand WHERE brand_id = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Brand brands = queryRunner.query(sql, new BeanHandler<Brand>(Brand.class), brandId);
        return brands;
    }

    //根据id修改品牌信息
    public int updateBrandById(String brandName,String brandLogo,String brandDesc,int brandId) throws SQLException {
        int i=0;
        String sql = "UPDATE tb_brand SET brand_name=?,brand_logo=?,brand_desc=? WHERE brand_id=?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, brandName, brandLogo, brandDesc, brandId);
        return i;
    }

}
